import datetime
import pandas as pd
from django.contrib.auth.hashers import make_password
from django.db import transaction, connection
from django.http import HttpResponse

from utils.exception import ParameterException


def organize_data(return_columns, inserted_rows):
    """
    :param return_columns: 返回的列名列表，例如 ['name', 'id']
    :param inserted_rows: 插入操作后从数据库查询到的行数据
    :return: 字典列表，每个字典包含一行数据的键值对
    """
    inserted_dicts = []
    for row in inserted_rows:
        row_dict = {column: value for column, value in zip(return_columns, row)}
        inserted_dicts.append(row_dict)
    return inserted_dicts


def set_difference(row):
    return list(set(row['fill_station_id']).difference(set(row['fill_station__id'])))


def organize_sql(table_name, dff1, insert_type=None, return_list=None):
    # 组织sql
    dff_columns = list(dff1)
    columns = ','.join(dff_columns)
    values = "VALUES({})".format(",".join(["%s" for _ in dff_columns]))
    return_str = ','.join(return_list)
    if insert_type:
        # 效率不高
        values_placeholders = ', '.join(['%s'] * len(dff_columns))
        all_values_placeholder = ', '.join(['({})'.format(values_placeholders) for _ in range(len(dff1))])
        insert_stmt = "INSERT INTO {} ({}) VALUES{} RETURNING {}".format(table_name, columns, all_values_placeholder, return_str)
    else:
        insert_stmt = "INSERT INTO {} ({}) {}".format(table_name, columns, values)

    return insert_stmt


def save_data(table_name, dff1, insert_type=None, return_list=None):
    """插入数据"""
    insert_stmt = organize_sql(table_name,dff1, insert_type=insert_type, return_list=return_list)
    @transaction.atomic
    def _save_data():
        with connection.cursor() as cursor:
            # 批量插入的两种模式
            try:
                if insert_type:
                    row_tuples = [tuple(row) for row in dff1.values]
                    cursor.execute(insert_stmt, *row_tuples)
                    inserted_rows = cursor.fetchall()
                else:
                    cursor.executemany(insert_stmt, dff1.values.tolist())
                    inserted_rows = []
            except:
                raise ParameterException(msg="导入失败！")
        return inserted_rows
    return _save_data()

def update_data(dff2, table_name, update_dff=pd.DataFrame(), relation_table='',rel_columns='', get_sql=None):
    """更新数据"""
    dff_dict = dff2.to_dict('records')
    sql_list = list()
    for r in dff_dict:
        now_kwargs = dict()
        set_sql = ""
        for i, j in r.items():
            if j is not None:
                if i != 'id':
                    now_kwargs[i] = j
                    set_sql += """{0} = %({0})s,""".format(i)

        sql_text = """
                UPDATE {table} SET {set_sql}
                id = %(id)s
                WHERE id = %(id)s
                """.format(table=table_name, set_sql=set_sql)

        if not get_sql:
            @transaction.atomic
            def _update_data():
                with connection.cursor() as cursor:
                    now_kwargs['id'] = r.get('id')
                    # cursor.execute(sql_text, id=r.get('id'), **now_kwargs)
                    try:
                        cursor.execute(sql_text, now_kwargs)
                        if not update_dff.empty:
                            sqls = "INSERT INTO {} ({}) {} ".format(relation_table, rel_columns, "VALUES(%s,%s)")
                            cursor.executemany(sqls, update_dff.values.tolist())
                    except:
                        raise ParameterException(msg="导入失败！")
            _update_data()
        else:
            now_kwargs['id']=r.get('id')
        sql_list.append({"now_kwargs": now_kwargs, "sql": sql_text})
    return dff2['id'].tolist() if not get_sql else sql_list

def groups_data(dff):
    groups = dff.groupby('id_type')
    groups_dict = {name: group for name, group in groups}
    dff1 = groups_dict.get('1', pd.DataFrame())
    dff2 = groups_dict.get('2', pd.DataFrame())
    return dff1, dff2


def import_data(dff, table_name, insert_type=None, return_list=[]):

    dff1, dff2 = groups_data(dff)
    if dff1.empty and dff2.empty:
       # raise ParameterException("导入失败！") 兼容异步
        return False

    if not dff1.empty:
        dff1['create_time'] = datetime.datetime.now()
        dff1.drop(columns=['id', 'id_type'], inplace=True)
        save_data(table_name,dff1, insert_type=insert_type, return_list=return_list)

    if not dff2.empty:
        dff2['update_time'] = datetime.datetime.now()
        dff2.drop(columns=['id_type'], inplace=True)
        update_data(dff2, table_name)
    return True

def improt_many(dff, suy_dff, table_name, relat_field, sub_dff, relation_table, relation_fields, remove_list=[]):
    # TODO 优化方法,目前只适用于指定表,指定字段使用,代码冗余度相对较高.灵活性较低
    """ 适用：一对多，多方已经提供数据id """
    dff1, dff2 = groups_data(dff)
    rel_columns = ', '.join(relation_fields)
    if dff1.empty and dff2.empty:
        return False
    if not dff1.empty:
        dff1['create_time'] = datetime.datetime.now()
        dff_sub = dff1[sub_dff]
        # 删除关联字段
        dff1.drop(columns=['id', 'id_type', relat_field], inplace=True)
        insert_stmt = organize_sql(table_name, dff1,insert_type=1, return_list=['name','id'])
        @transaction.atomic
        def save_data():
            with connection.cursor() as cursor:
                flat_values_list = tuple(dff1.values.flatten())
                try:
                    cursor.execute(insert_stmt, flat_values_list)
                except:
                    raise ParameterException(msg='导入失败')

                inserted_rows = cursor.fetchall()
                inserted_dict =  [{'name': name, 'id': id} for name, id in inserted_rows]
                dff3 = pd.DataFrame(inserted_dict)
                # 处理关系表
                dff4 = pd.merge(left=dff_sub, right=dff3, how='inner',on='name')
                dff5 = dff4.explode(relat_field)
                dff6 = dff5[['id', relat_field]]
                # 关系表同步关系
                sqls = "INSERT INTO {} ({}) {} ".format(relation_table,rel_columns, "VALUES(%s,%s)")
                try:
                    cursor.executemany(sqls, dff6.values.tolist())
                except:
                    raise ParameterException(msg='导入失败')
        save_data()

    if not dff2.empty:
        if remove_list:
            dff2.drop(columns=remove_list, inplace=True)
        if not suy_dff.empty:
            merged_df = pd.merge(left=dff2, right=suy_dff, on='id',how='left')
        else:
            merged_df = dff2
        merged_df['update_time'] = datetime.datetime.now()
        # 组件提交sql
        update_dff = pd.DataFrame()
        update_dff[relation_fields[1]] = merged_df.apply(set_difference, axis=1)
        update_dff[relation_fields[0]] = merged_df['id']
        update_dff = update_dff.explode(relation_fields[1])
        update_dff = update_dff.dropna(subset=[relation_fields[1]])
        merged_df.drop(columns=['id_type',relat_field, 'fill_station__id'], inplace=True)
        update_data(merged_df,table_name, update_dff=update_dff, relation_table=relation_table,rel_columns=rel_columns)
    return True

def execute_sql(cursor, insert_stmt, dff1, return_list):
    try:
        cursor.execute(insert_stmt, dff1.values.flatten())
    except:
        raise ParameterException(msg='导入失败')
    inserted_rows = cursor.fetchall()
    inserted_dict = organize_data(return_list, inserted_rows)
    dff = pd.DataFrame(inserted_dict)
    return dff


def import_many_t(dff,table_name, inner_field, return_list, insert_type,):
    """ 适用：一对多，多方不提供数据,关联另一个表中"""
    dff1, dff2 = groups_data(dff)
    if dff1.empty and dff2.empty:
        return False

    if not dff1.empty:
        dff1['create_time'] = datetime.datetime.now()
        dffs = dff1.copy(deep=True)
        dff1.drop(columns=['id', 'id_type', 'role_id'], inplace=True)
        insert_stmt = organize_sql(table_name, dff1, insert_type=insert_type, return_list=return_list)
        @transaction.atomic
        def save_data():
            with connection.cursor() as cursor:

                dff3 = execute_sql(cursor, insert_stmt, dff1, return_list)
                # 处理关系表
                dff1['id_number'] = dff1['id_number'].astype('str')  # 转换为字符串类型
                dff3['id_number'] = dff3['id_number'].astype('str')  # 转换为字符串类型
                dff4 = pd.merge(left=dff1, right=dff3, how='inner', on=inner_field)
                dff5 = dff4[['id', 'id_number','phone', 'name']]
                dff5 = dff5.rename(columns={"id":"other_id","phone":"mobile", "id_number":"username"})
                dff5['type'] = '2'
                dff5['password'] = dff5['username'].apply(lambda x : make_password('Xb@7268066'))
                # user表数据
                # 验证是否已经存在, 逻辑关联删除用户数据这里不用判断直接
                insert_stmt_2 = organize_sql('system_users', dff5, insert_type=insert_type, return_list=['id', 'username'])
                dff6 = execute_sql(cursor, insert_stmt_2, dff5, ['id', 'username'])
                dff6['username'] = dff6['username'].astype('str')  # 转换为字符串类型
                dffs['id_number'] = dffs['id_number'].astype('str')  # 转换为字符串类型
                dffs.drop(columns=['id'], inplace=True)
                # 返回id, id_number
                dff6 = pd.merge(left=dff6, right=dffs, how='inner', left_on="username", right_on="id_number")
                # 更新表中users_id
                dff_updata = pd.merge(left=dff6, right=dff3,left_on="username", right_on="id_number", how='inner')
                dff_updata = dff_updata.rename(columns={"id_x":"user_id", "id_y":"id"})
                dff1_updata = dff_updata[["id", "user_id"]]
                sql_dict = update_data(dff1_updata, table_name,get_sql=True)
                try:
                    for sqls in sql_dict:
                        cursor.execute(sqls.get("sql"), sqls.get("now_kwargs"))
                except:
                    raise ParameterException(msg='导入失败')

                # 关系表同步关系
                dff7 = dff6[['id', 'role_id']]
                sqls = "INSERT INTO {} ({}) {} ".format('system_users_role', "users_id,role_id", "VALUES(%s,%s)")
                try:
                    cursor.executemany(sqls, dff7.values.tolist())
                except:
                    raise ParameterException(msg='导入失败')
        save_data()
    if not dff2.empty:
        # 覆盖逻辑
        # 先更新主表，后更新user和role 表
        pass
    return True

def export_data(df, file_name):
    response = HttpResponse(content_type='application/ms-excel')
    response['Content-Disposition'] = 'attachment; filename="{}"'.format(file_name)
    with pd.ExcelWriter(response, engine='openpyxl') as writer:
        df.to_excel(writer, index=False, sheet_name='Sheet1')
    return response